﻿
CREATE FUNCTION [dbo].[GoodsFlow]
(
	@DepartmentId int, -- код подразделения
	@GoodsId int,
	@PeriodBegin smalldatetime,
	@PeriodEnd smalldatetime,
	@WithAggregate bit = 0		-- =1- расчет с агрегированием товаров у которых одинаковые AssortmentId, RetailPack (точнее DefekturaAggregate = 1)
)
RETURNS @tblGoodsFlow TABLE (
	[DepartmentId] [int] NOT NULL ,
	[GoodsId] [int] NOT NULL ,
	[DocDate] smalldatetime NOT NULL,
	[QnttyInput] real NULL,			--приход
	[QnttyOutput] real NULL,		--расход
	[Turnover] real NULL,				--оборот за день
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL,
	[DayRest] real NULL,				--остаток на конец дня
	[FullDays] [int] NULL
	PRIMARY KEY (DocDate)
)
AS
BEGIN
/* Черноуосов ОП 13.07.06 Движение товара за период по подразделению */
DECLARE @intSaleDocTypeId int
DECLARE @realTurnover real, @realSaleQntty real, @realQnttyInput real, @realQnttyOutput real
DECLARE @datDocDate smalldatetime, @decSaleSum [decimal](19, 2)
DECLARE @datPrevFullDate smalldatetime, @intFullDays int, @intDayRest int
DECLARE @intAssortmentId int, @intRetailPack int, @bitDefekturaAggregate bit

DECLARE @tblAggregates TABLE (
	[GoodsId] [int] NOT NULL ,
	AggrGoodsId [int] NOT NULL
	PRIMARY KEY (GoodsId)
) 

DECLARE @tblSearchGoods TABLE (
	[GoodsId] [int] NOT NULL,
	ParentRetailPack int,
	ChildRetailPack int
	PRIMARY KEY (GoodsId)
)

DECLARE @tblBeginRest TABLE (
	[QnttyInput] real NULL,			--приход
	[QnttyOutput] real NULL,		--расход
	[Turnover] real NULL,				--оборот за день
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL
)

SELECT @intDayRest = 0, @intSaleDocTypeId = 3 --Кассовый чек 

--select @PeriodBegin = '2005-11-01', @PeriodEnd = '2006-01-31'

IF @WithAggregate = 1 
BEGIN

	SELECT @intAssortmentId = AssortmentId, @intRetailPack = RetailPack, @bitDefekturaAggregate = DefekturaAggregate
	FROM CA.Goods WHERE Id = @GoodsId
	IF @bitDefekturaAggregate = 1
	BEGIN
		INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
		SELECT Id AS GoodsId, @GoodsId AS AggrGoodsId
		FROM CA.Goods WHERE AssortmentId = @intAssortmentId AND RetailPack = @intRetailPack AND DefekturaAggregate = 1

	END ELSE BEGIN
		INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
		SELECT @GoodsId, @GoodsId
	END
END ELSE BEGIN
	INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
	SELECT @GoodsId, @GoodsId
END

--TODO переделать при новой разборке
INSERT INTO @tblSearchGoods( GoodsId, ParentRetailPack, ChildRetailPack)
SELECT G.Id, G.RetailPack, G.RetailPack --только без родителей
FROM @tblAggregates A INNER JOIN CA.Goods G ON A.GoodsId = G.Id
LEFT OUTER JOIN CA.Goods P ON G.Id = P.ChildId WHERE P.Id Is Null
UNION
SELECT C.Id, P.RetailPack, C.RetailPack --только потомки
FROM CA.Goods P INNER JOIN CA.Goods C ON P.ChildId = C.Id
INNER JOIN @tblAggregates A ON A.GoodsId = C.Id


--определяем данные на начало периода
INSERT INTO @tblBeginRest( Turnover, QnttyInput, QnttyOutput, SaleQntty, SaleSum)
SELECT
SUM(Qntty) AS Turnover,
SUM(CASE WHEN Qntty > 0 THEN Qntty ELSE 0.0 END) AS QnttyInput,
SUM(CASE WHEN Qntty < 0 THEN -1.0 * Qntty ELSE 0.0 END) AS QnttyOutput,
SUM(CASE DocTypeId WHEN @intSaleDocTypeId THEN -1.0 * Qntty ELSE 0.0 END) AS SaleQntty,
SUM(CASE DocTypeId WHEN @intSaleDocTypeId THEN -1.0 * RetailSum ELSE 0.0 END) AS SaleSum
FROM (
	SELECT F.DocTypeId, F.RetailSum,
--количество по разобранным товарам приводим к родителям	
	cast(F.Qntty as real) * cast(G.ChildRetailPack as real) / cast(G.ParentRetailPack as real) AS Qntty
	FROM dbo.vwGoodsFlow F INNER JOIN @tblSearchGoods G ON F.GoodsId = G.GoodsId
	WHERE F.DocDate < @PeriodBegin
	AND (DepartmentId = @DepartmentId OR @DepartmentId IS NULL) 
) T

DELETE FROM @tblBeginRest WHERE QnttyInput Is Null

--расчитываем данные за период
DECLARE CrsrByDate CURSOR FAST_FORWARD FOR
SELECT dateadd(day, -1, @PeriodBegin ) AS DocDate, Turnover, QnttyInput, QnttyOutput, SaleQntty, SaleSum FROM @tblBeginRest
UNION ALL
SELECT DocDate, SUM(Qntty) AS Turnover,
SUM(CASE WHEN Qntty > 0.0 THEN Qntty ELSE 0.0 END) AS QnttyInput,
SUM(CASE WHEN Qntty < 0.0 THEN -1.0 * Qntty ELSE 0.0 END) AS QnttyOutput,
SUM(CASE DocTypeId WHEN @intSaleDocTypeId THEN -1.0 * Qntty ELSE 0.0 END) AS SaleQntty,
SUM(CASE DocTypeId WHEN @intSaleDocTypeId THEN -1.0 * RetailSum ELSE 0.0 END) AS SaleSum
FROM (
	SELECT dbo.NormalizeDate(F.DocDate) AS DocDate, F.DocTypeId, F.RetailSum,
--количество по разобранным товарам приводим к родителям	
	cast(F.Qntty as real) * cast(G.ChildRetailPack as real) / cast(G.ParentRetailPack as real) AS Qntty
	FROM dbo.vwGoodsFlow F INNER JOIN @tblSearchGoods G ON F.GoodsId = G.GoodsId
	WHERE F.DocDate >= @PeriodBegin AND F.DocDate < dateadd(day, 1, @PeriodEnd)
	AND (DepartmentId = @DepartmentId OR @DepartmentId IS NULL) 
) T
GROUP BY DocDate
ORDER BY DocDate

OPEN CrsrByDate
FETCH NEXT FROM CrsrByDate INTO @datDocDate, @realTurnover, @realQnttyInput, @realQnttyOutput, @realSaleQntty, @decSaleSum

WHILE (@@FETCH_STATUS = 0)
BEGIN

	SET @intDayRest = @intDayRest + @realTurnover

	IF @datPrevFullDate IS NULL SET @intFullDays = 0
	ELSE SET @intFullDays = datediff(day, @datPrevFullDate, @datDocDate)

	INSERT INTO @tblGoodsFlow( DepartmentId, GoodsId, DocDate, Turnover, QnttyInput, QnttyOutput, SaleQntty, SaleSum, DayRest, FullDays)
	VALUES (@DepartmentId, @GoodsId, @datDocDate, @realTurnover, @realQnttyInput, @realQnttyOutput, @realSaleQntty, @decSaleSum, @intDayRest, @intFullDays)

	IF @intDayRest = 0 SET @datPrevFullDate = NULL
	ELSE SET @datPrevFullDate = @datDocDate

	FETCH NEXT FROM CrsrByDate INTO @datDocDate, @realTurnover, @realQnttyInput, @realQnttyOutput, @realSaleQntty, @decSaleSum

END

CLOSE CrsrByDate
DEALLOCATE CrsrByDate
	RETURN 
END
